/**
 * 
 */
package com.xuyue;

import java.io.*;
import java.util.Arrays;
import java.util.HashMap;

/**
 * 创建可重复执行脚本
 * @author tony
 *
 */
public class GenerateSqlScripts {
	
	/**
	 * 维护oracle数据类型到mssql数据类型的映射关系
	 */
	@SuppressWarnings("serial")
	private static final HashMap<String, String> MAPPING =new HashMap<String, String>(){{  
	      put("VARCHAR2","VARCHAR");  
	      put("NUMBER","NUMERIC");       
	      put("TIMESTAMP(6)","DATETIME");       
	      put("RAW(16)","replace(newid(),'-','')");       
	      put("DATE","DATETIME");       
	}};
	
	/**
	 * 将oracle数据类型转成mssql
	 * @param str
	 * @return
	 */
	private static String translateToMsqlDataType(String str) {
		for (String key : MAPPING.keySet()) {
			str=str.replace(key,MAPPING.get(key));
		}
		return str;
	}
	
	/**
	 * 生成mssql可重复执行table脚本
	 * @param oralceOrginalFileName--转换前文件名
	 * @param oralceTargetFileName--转换后文件名
	 * @param tableCount--表总数量
	 * @throws Exception
	 */
	private static void createOracleTableSql(String oralceOrginalFileName,String oralceTargetFileName,int tableCount) throws Exception{
		String inPath = GenerateSqlScripts.class.getClassLoader().getResource(oralceOrginalFileName).getPath();
		inPath =inPath.replaceAll("/target/classes/", "/src/main/resources/");
		String outPath= GenerateSqlScripts.class.getClassLoader().getResource(oralceTargetFileName).getPath();
		outPath =outPath.replaceAll("/target/classes/", "/src/main/resources/");
		System.out.println("inPath:"+inPath);
		System.out.println("outPath:"+outPath);
		System.out.println("===============================================================");
        //读取文件(字符流)
        BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(inPath),"UTF-8"));
        //写入相应的文件
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outPath),"UTF-8"));
        //读取数据
        //循环取出数据
        String str = null;
        boolean tbbody=false;
        int currentCount=0;
        while ((str = in.readLine()) != null) {
        	if(str.indexOf("default")!=-1) {
        		str=str.replaceAll("'", "''");
        	}
            String[] split = str.split("\\s+");//\\s表示 空格,回车,换行等空白符, +号表示一个或多个的意思
            System.out.println(Arrays.toString(split));
            if(str.startsWith("create table")){
            	currentCount++;
                tbbody=true;
                if(currentCount!=1) {
                	out.write("END IF;");
                    out.newLine();
                    out.write("END;");
                    out.newLine();
                    out.write("/");
                    out.newLine();
                    out.newLine();
                }
                out.write("DECLARE num NUMBER;");
                out.newLine();
                out.write("BEGIN");
                out.newLine();
                out.write("  select count(1) into num from user_tables t where t.TABLE_NAME = UPPER('"+split[2].replace(";","")+"');");
                out.newLine();
                out.write("  if num<1 THEN");
                out.newLine();
                out.write("EXECUTE IMMEDIATE '");
                out.newLine();
                out.write(str);
                out.newLine();
                //正在写入tbbody
            }else if(tbbody){
                if(str.trim().endsWith(";")){
                    out.write(str.replace(";","")+"';");
                    tbbody=false;
                }else{
                    out.write(str);
                }
                out.newLine();
            }else{
                if(str.trim().equals("")){
                    continue;
                }
                out.write("EXECUTE IMMEDIATE '"+str.replace(";","").replace("'","''")+"';");
                out.newLine();
            }
        }
        if(currentCount==tableCount) {//最后一张表
        	out.write("END IF;");
            out.newLine();
            out.write("END;");
            out.newLine();
            out.write("/");
            out.newLine();
            out.newLine();
        }
        //清楚缓存
        out.flush();
        //关闭流
        in.close();
        out.close();
    }
	
	/**
	 * 生成oracle可重复执行table脚本
	 * @param oralceOrginalFileName
	 * @param mssqlTargetFileName
	 * @param tableCount
	 * @throws Exception
	 */
	private static void createMssqlTableSql(String oralceOrginalFileName,String mssqlTargetFileName,int tableCount) throws Exception{
		String inPath = GenerateSqlScripts.class.getClassLoader().getResource(oralceOrginalFileName).getPath();
		inPath =inPath.replaceAll("/target/classes/", "/src/main/resources/");
		String outPath= GenerateSqlScripts.class.getClassLoader().getResource(mssqlTargetFileName).getPath();
		outPath =outPath.replaceAll("/target/classes/", "/src/main/resources/");
		System.out.println("inPath:"+inPath);
		System.out.println("outPath:"+outPath);
		System.out.println("===============================================================");
        //读取文件(字符流)
        BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(inPath),"UTF-8"));
        //写入相应的文件
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outPath),"UTF-8"));
        //读取数据
        //循环取出数据
        String str = null;
        int currentCount=0;
        while ((str = in.readLine()) != null) {
        	str=translateToMsqlDataType(str);
            String[] split = str.split("\\s+");
            System.out.println(Arrays.toString(split));
            if(str.startsWith("create table")){
            	currentCount++;
                if(currentCount!=1) {
                	out.write("END;");
                    out.newLine();
                    out.write("GO");
                    out.newLine();
                    out.newLine();
                }
                out.write("IF NOT EXISTS(SELECT * FROM sysobjects WHERE id =  object_id(N'["+split[2].replace(";","")+"]') AND xtype = 'U') begin");
                out.newLine();
                out.write(str);
                out.newLine();
            }else if(str.startsWith("comment on table")){
                out.write("execute sp_addextendedproperty 'MS_Description',"+split[5].replace(";","")+",'user','dbo','table','"+split[3]+"',null,null;");
                out.newLine();
            }else if(str.startsWith("comment on column")){
                String[] t_c=split[3].split("\\.");
                System.out.println("tc"+Arrays.toString(t_c));
                out.write("execute sp_addextendedproperty 'MS_Description',"+split[5].replace(";","")+",'user','dbo','table','"+t_c[0]+"','column','"+t_c[1]+"';");
                out.newLine();
			} /*
				 * else if(str.startsWith("create index")||str.startsWith("alter table")) {
				 * 
				 * }
				 */
            else{
                out.write(str);
                out.newLine();
            }
        }
        if(currentCount==tableCount) {//最后一张表
        	out.write("END;");
            out.newLine();
            out.write("GO");
            out.newLine();
            out.newLine();
        }
        //清楚缓存
        out.flush();
        //关闭流
        in.close();
        out.close();
    }
	
	/**
	 * 生成添加字段可重复执行脚本
	 * @param oralceOrginalFileName
	 * @param dbType
	 * @param targetFileName
	 * @throws IOException
	 */
	private static void createAddColumnSql(String oralceOrginalFileName,String dbType,String targetFileName) throws IOException {
		String inPath = GenerateSqlScripts.class.getClassLoader().getResource(oralceOrginalFileName).getPath();
		inPath =inPath.replaceAll("/target/classes/", "/src/main/resources/");
		String outPath= GenerateSqlScripts.class.getClassLoader().getResource(targetFileName).getPath();
		outPath =outPath.replaceAll("/target/classes/", "/src/main/resources/");
		System.out.println("inPath:"+inPath);
		System.out.println("outPath:"+outPath);
		System.out.println("===============================================================");
        //读取文件(字符流)
        BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(inPath),"UTF-8"));
        //写入相应的文件
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outPath),"UTF-8"));
        String str = null;
        while ((str = in.readLine()) != null) {
            if("oracle".equals(dbType)) {
            	String[] split = str.split(" , ");
                System.out.println(Arrays.toString(split));
                String tableName=split[0];
                String columnName=split[1];
                String dataType=split[2];
                dataType=dataType.replaceAll("'", "''");
                String comment=split[3];
                out.write("DECLARE num NUMBER;");
                out.newLine();
                out.write("BEGIN");
                out.newLine();
                out.write("  SELECT COUNT(*) into num FROM USER_TAB_COLUMNS WHERE TABLE_NAME = upper('"+tableName+"') AND COLUMN_NAME = upper('"+columnName+"');");
                out.newLine();
                out.write("  if num<1 THEN");
                out.newLine();
                out.write(" execute immediate 'alter table "+tableName+" add  "+columnName+"      "+dataType+"';");  
                out.newLine();
                out.write(" execute immediate 'comment on column "+tableName+"."+columnName+" is ''"+comment+"''';");
                out.newLine();
                out.write("  END IF;");
                out.newLine();
                out.write("END;");
                out.newLine();
                out.write("/");
                out.newLine();
    		}else {
    			str=translateToMsqlDataType(str);
    			String[] split = str.split(" , ");
                System.out.println(Arrays.toString(split));
                String tableName=split[0];
                String columnName=split[1];
                String dataType=split[2];
                String comment=split[3];
    			out.write("IF NOT EXISTS(select  * from   syscolumns   where   id=object_id('"+tableName+"')   and   name='"+columnName+"'  ) begin");
                out.newLine();
                out.write("　　alter table "+tableName+" add "+columnName+" "+dataType+";");
                out.newLine();
                out.write("    execute sp_addextendedproperty 'MS_Description','"+comment+"','user','dbo','table','"+tableName+"','column','"+columnName+"';");
                out.newLine();
                out.write("end;");
                out.newLine();
    		}
        }   
        //清楚缓存
        out.flush();
        //关闭流
        in.close();
        out.close();
	}

	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
//		createOracleTableSql("oracle_table.sql", "target_oracle_table.sql",1);
//		createMssqlTableSql("oracle_table.sql", "target_mssql_table.sql",1);
		createAddColumnSql("oracle_column.sql","oracle","target_oracle_column.sql");
		createAddColumnSql("oracle_column.sql","mssql","target_mssql_column.sql");
	}

}
